<?php

class Table extends Zend_Db_Table_Abstract
{
	protected $_name = 'projectstables';
	
    public function hasTable($name)
    {
        $select = $this->select()->where('name="'.$name.'"');
        $result = $this->fetchAll($select)->toArray(); 
        
        if(isset($result[0]))
        	return true;

        return false;
    }

	public function insertTable($name,$description,$projectId)
	{
		//cadastra a tabela em projectstables
		$data = array(
		            'name'=>$name,
		            'description' => $description,
		            'creationDate' => date('l js \of F Y h:i:s A'),
		            'projects_id' => $projectId
					);

		$this->insert($data);
		//cria a tabela
		$db = Zend_Registry::get('db');
		$query = 'CREATE TABLE '.$name.' (id int NOT NULL AUTO_INCREMENT,
										PRIMARY KEY (`id`)) engine=InnoDB;';
		$stmt = $db->query($query);
	}

	public function getByProjectId($id)
	{
        $select = $this->select()->where('projects_id = '.$id);
        return ($this->fetchAll($select)->toArray());      
	}

	public function getAll()
	{
        $select = $this->select();
        return ($this->fetchAll($select)->toArray());      
	}

	//pega o conteúdo da tabela específica (implementar)
	public function getOuterTable($tableId)
	{
		if(!is_numeric($tableId))
			return false;

		$tablesContents = $this->getContentById($tableId);
		//definiçao das colunas da tabela especificada]
		$tableData = array(); 
		$tableData['schema'] = $this->getTableSchema($tablesContents['name']);
		$tableData['content'] = $this->getTableContent($tablesContents['name']);		

		return $tableData;
	}

	//adiciona uma coluna na tabela especificada
	public function addCollumnOuterTable($projectId='0',$tableName='none',$collumnName='none',$collumnType='string')
	{
		$realType = ($collumnType=='string') ? 'varchar(255)' : 'integer';
		$db = Zend_Registry::get('db');
        $query = 'ALTER TABLE `'.$tableName.'` ADD '.$collumnName.' '.$realType;  

        $tableSchema = $db->query($query);
        return $tableSchema->fetchAll();
	}


	//remove uma tabela externa
	public function dropOuterTable($tableId)
	{
		//antes de deletar pega o nome da outra tabela
		$resultTableInfo = $this->getOuterTableInfoById($tableId);
		
		//remove referencia da tabela de Tabelas
		$where = 'id = '.$tableId;
		$this->delete($where);
		//remove a tabela do banco de dados
		$db = Zend_Registry::get('db');
        $query = 'DROP TABLE `'.$resultTableInfo[0]['name'];    
        $tableSchema = $db->query($query);
        return $tableSchema->fetchAll();
	}

	//pega o conteúdo de uma tabela
	public function getContentById($tableId)
	{
		$select = $this->select()->where('id="'.$tableId.'"');
		$result = $this->fetchAll($select)->toArray();
		return $result[0];
	}

	public function getOuterTableInfoById($id)
	{
		$select = $this->select()->where('`id`="'.$id.'"');
		return $this->fetchAll($select)->toArray();
	}

	//funcao de compatibilidade utilizar getOuterTableInfoById
	public function getTableInfoById($id)
	{
		$select = $this->select()->where('`id`="'.$id.'"');
		return $this->fetchAll($select)->toArray();
	}

	//pega os campos de uma tabela
	private function getTableSchema($tableName)
	{
		$db = Zend_Registry::get('db');
        $query = 'DESCRIBE `'.$tableName.'`';    
        $tableSchema = $db->query($query);
        return $tableSchema->fetchAll();
	}

	private function getTableContent($tableName)
	{
		$db = Zend_Registry::get('db');
        $query = 'SELECT * FROM `'.$tableName.'` ORDER BY id DESC';    
        $tableSchema = $db->query($query);
        return $tableSchema->fetchAll();
	}

	public function alterOuterTableDefinition($projectId=null,$tableName=null,$collumns=null)
	{
		if(isset($tableName) && isset($collumns) && isset($projectId))
		{
			$query = "";
			foreach($collumns as $old => $new)
			{	
				$type = ($new['cellType']=='string')? 'VARCHAR(255)':'INTEGER';
				if($new['cellName'] == '')
					$new['cellName'] = 'desinformado';
				$query.= 'CHANGE '.$old.' '.$new['cellName'].' '.$type.',';
			}
			$query = substr($query,0,-1);

			$db = Zend_Registry::get('db');
	        $query = 'ALTER TABLE `'.$tableName.'` '.$query;  

	        $tableSchema = $db->query($query);
	        return $tableSchema->fetchAll();


	        return true;
        }
        return false;
	}
	//insere as linhas n no banco de dados
	public function insertOuterData($tableName,$data)
	{
		$query = array();
		foreach($data as $collumnName => $collumnValue)
		{
			$query[$collumnName] = $collumnValue['cellName'];
		}

		$db = Zend_Registry::get('db');
		try
		{
			$db->insert($tableName,$query);
		}
		catch (Zend_Db_Exception $e)
		{
			$this->view->error = 'Erro durante a inserao da linha.';            
		}
	}
}
?>